package com.erp.utils;

import com.erp.annotation.ExcelAttribute;
import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;

import java.io.InputStream;
import java.io.OutputStream;
import java.io.Serializable;
import java.lang.reflect.Field;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

/**
 * excel工具类
 *
 * @Author lst
 * @Date 2020-06-22 15:38
 * @Version 1.0
 **/
public class ExcelUtil<T> implements Serializable {

    /**
     * 序列码
     */
    private static final long serialVersionUID = 8937622642443164168L;

    /**
     * 日志
     */
    private static final Logger LOGGER = Logger.getLogger(ExcelUtil.class);

    /**
     * excel2003中每个sheet中最多有65536行
     */
    private static final int SHEET_SIZE = 65536;

    private Class<T> clazz;

    public ExcelUtil(Class<T> clazz) {
        this.clazz = clazz;
    }

    /**
     * 导入Excel
     *
     * @param sheetName
     * @param input
     * @return List<T>
     */
    public List<T> importExcel(String sheetName, InputStream input) {
        LOGGER.info("开始导入Excel");
        List<T> list = new ArrayList<T>();
        try {
            HSSFWorkbook workbook = new HSSFWorkbook(input);
            HSSFSheet sheet = workbook.getSheet(sheetName);
            if (!sheetName.trim().equals("")) {
                // 如果指定sheet名,则取指定sheet中的内容.
                sheet = workbook.getSheet(sheetName);
            }
            if (sheet == null) {
                // 如果传入的sheet名不存在则默认指向第1个sheet.
                sheet = workbook.getSheetAt(0);
            }
            int rows = sheet.getPhysicalNumberOfRows();
            // 有数据时才处理.
            if (rows > 0) {
                // 得到类的所有field.
                Field[] allFields = clazz.getDeclaredFields();
                // 定义一个map用于存放列的序号和field.
                Map<Integer, Field> fieldsMap = new HashMap<Integer, Field>();
                for (Field field : allFields) {
                    // 将有注解的field存放到map中.
                    if (field.isAnnotationPresent(ExcelAttribute.class)) {
                        ExcelAttribute attr = field
                                .getAnnotation(ExcelAttribute.class);
                        // 获得列号
                        int col = getExcelCol(attr.column());
                        // 设置类的私有字段属性可访问.
                        field.setAccessible(true);
                        fieldsMap.put(col, field);
                    }
                }
                // 从第2行开始取数据,默认第一行是表头.
                for (int i = 1; i < rows; i++) {
                    HSSFRow row = sheet.getRow(i);
                    int cellNum = row.getPhysicalNumberOfCells();
                    T entity = null;
                    for (int j = 0; j < cellNum; j++) {
                        HSSFCell cell = row.getCell(j);
                        if (cell == null) {
                            continue;
                        }
                        String value = "";
                        switch (cell.getCellType()) {
                            // 数字.
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                //如果为时间格式的内容.
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {
                                    //注：format格式 yyyy-MM-dd hh:mm:ss 中小时为12小时制，若要24小时制，则把小h变为H即可，yyyy-MM-dd HH:mm:ss.
                                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd hh:mm:ss");
                                    value = sdf.format(HSSFDateUtil.getJavaDate(cell.
                                            getNumericCellValue())).toString();
                                    break;
                                } else {
                                    value = new DecimalFormat("0").format(cell.getNumericCellValue());
                                }
                                break;
                            // 字符串.
                            case HSSFCell.CELL_TYPE_STRING:
                                value = cell.getStringCellValue();
                                break;
                            // Boolean.
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                value = cell.getBooleanCellValue() + "";
                                break;
                            // 公式.
                            case HSSFCell.CELL_TYPE_FORMULA:
                                value = cell.getCellFormula() + "";
                                break;
                            // 空值.
                            case HSSFCell.CELL_TYPE_BLANK:
                                value = "";
                                break;
                            // 故障.
                            case HSSFCell.CELL_TYPE_ERROR:
                                value = "非法字符";
                                break;
                            default:
                                value = "未知类型";
                                break;
                        }
                        if (value.equals("")) {
                            continue;
                        }
                        // 如果不存在实例则新建.
                        entity = (entity == null ? clazz.newInstance() : entity);
                        // 从map中得到对应列的field.
                        Field field = fieldsMap.get(j);
                        // 取得类型,并根据对象类型设置值.
                        Class<?> fieldType = field.getType();
                        if (String.class == fieldType) {
                            field.set(entity, String.valueOf(value));
                        } else if ((Integer.TYPE == fieldType)
                                || (Integer.class == fieldType)) {
                            field.set(entity, Integer.parseInt(value));
                        } else if ((Long.TYPE == fieldType)
                                || (Long.class == fieldType)) {
                            field.set(entity, Long.valueOf(value));
                        } else if ((Float.TYPE == fieldType)
                                || (Float.class == fieldType)) {
                            field.set(entity, Float.valueOf(value));
                        } else if ((Short.TYPE == fieldType)
                                || (Short.class == fieldType)) {
                            field.set(entity, Short.valueOf(value));
                        } else if ((Double.TYPE == fieldType)
                                || (Double.class == fieldType)) {
                            field.set(entity, Double.valueOf(value));
                        } else if (Character.TYPE == fieldType) {
                            if ((value != null) && (value.length() > 0)) {
                                field.set(entity, Character.valueOf(value.charAt(0)));
                            }
                        }
                    }
                    if (entity != null) {
                        list.add(entity);
                    }
                }
            }
        } catch (Exception e) {
            LOGGER.warn("导入Excel异常:" + e);
        }
        LOGGER.info("成功导入Excel");
        return list;
    }

    /**
     * 导出数据至Excel
     *
     * @param list
     * @param sheetName
     * @param sheetSize
     * @param output
     * @return
     */
    public boolean exportExcel(List<T> list, String sheetName, int sheetSize, OutputStream output) {
        LOGGER.info("开始导出数据至Excel");
        // 得到所有定义字段.
        Field[] allFields = clazz.getDeclaredFields();
        List<Field> fields = new ArrayList<Field>();
        // 得到所有field并存放到一个list中.
        for (Field field : allFields) {
            if (field.isAnnotationPresent(ExcelAttribute.class)) {
                fields.add(field);
            }
        }
        // 产生工作薄对象
        HSSFWorkbook workbook = new HSSFWorkbook();
        // excel2003中每个sheet中最多有65536行,为避免产生错误所以加这个逻辑.
        if (sheetSize > SHEET_SIZE || sheetSize < 1) {
            sheetSize = SHEET_SIZE;
        }
        // 取出一共有多少个sheet.
        double sheetNo = Math.ceil(list.size() / sheetSize);
        for (int index = 0; index <= sheetNo; index++) {
            // 产生工作表对象
            HSSFSheet sheet = workbook.createSheet();
            if (sheetNo == 0) {
                workbook.setSheetName(index, sheetName);
            } else {
                // 设置工作表的名称.
                workbook.setSheetName(index, sheetName + index);
            }
            HSSFRow row;
            // 产生单元格.
            HSSFCell cell;
            // 产生一行.
            row = sheet.createRow(0);
            // 写入各个字段的列头名称.
            for (int i = 0; i < fields.size(); i++) {
                Field field = fields.get(i);
                ExcelAttribute attr = field
                        .getAnnotation(ExcelAttribute.class);
                // 获得列号.
                int col = getExcelCol(attr.column());
                // 创建列.
                cell = row.createCell(col);
                // 设置列中写入内容为String类型.
                cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                // 写入列名
                cell.setCellValue(attr.name());
                // 如果设置了提示信息则鼠标放上去提示.
                if (!attr.prompt().trim().equals("")) {
                    // 这里默认设了2-101列提示.
                    setHSSFPrompt(sheet, "", attr.prompt(), 1, 100, col, col);
                }

            }
            int startNo = index * sheetSize;
            int endNo = Math.min(startNo + sheetSize, list.size());
            // 写入各条记录,每条记录对应excel表中的一行
            for (int i = startNo; i < endNo; i++) {
                row = sheet.createRow(i + 1 - startNo);
                // 得到导出对象.
                T vo = (T) list.get(i);
                for (int j = 0; j < fields.size(); j++) {
                    // 获得field.
                    Field field = fields.get(j);
                    // 设置实体类私有属性可访问
                    field.setAccessible(true);
                    ExcelAttribute attr = field
                            .getAnnotation(ExcelAttribute.class);
                    try {
                        // 创建cell
                        cell = row.createCell(getExcelCol(attr.column()));
                        cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                        // 如果数据存在就填入,不存在填入空格.
                        cell.setCellValue(field.get(vo) == null ? "" : String.valueOf(field.get(vo)));
                    } catch (IllegalArgumentException e) {
                        e.printStackTrace();
                    } catch (IllegalAccessException e) {
                        e.printStackTrace();
                    }
                }
            }
        }
        try {
            output.flush();
            workbook.write(output);
            output.close();
            LOGGER.info("成功导出数据至Excel");
            return true;
        } catch (Exception e) {
            LOGGER.warn("导出数据至Excel异常:" + e);
            return false;
        }
    }


    /**
     * 将EXCEL中A,B,C,D,E列映射成0,1,2,3
     *
     * @param col
     * @return int
     */
    public static int getExcelCol(String col) {
        col = col.toUpperCase();
        // 从-1开始计算,字母重1开始运算。这种总数下来算数正好相同.
        int count = -1;
        char[] cs = col.toCharArray();
        for (int i = 0; i < cs.length; i++) {
            count += (cs[i] - 64) * Math.pow(26, cs.length - 1 - i);
        }
        return count;
    }

    /**
     * 设置单元格提示信息
     *
     * @param sheet         要设置的sheet
     * @param promptTitle   标题
     * @param promptContent 内容
     * @param firstRow      开始行
     * @param endRow        结束行
     * @param firstCol      开始列
     * @param endCol        结束列
     * @return HSSFSheet
     */
    public static HSSFSheet setHSSFPrompt(HSSFSheet sheet, String promptTitle, String promptContent, int firstRow, int endRow, int firstCol, int endCol) {
        // 构造constraint对象.
        DVConstraint constraint = DVConstraint.createCustomFormulaConstraint("DD1");
        // 四个参数分别是：起始行、终止行、起始列、终止列.
        CellRangeAddressList regions = new CellRangeAddressList(firstRow, endRow, firstCol, endCol);
        // 数据有效性对象
        HSSFDataValidation data_validation_view = new HSSFDataValidation(regions, constraint);
        data_validation_view.createPromptBox(promptTitle, promptContent);
        sheet.addValidationData(data_validation_view);
        return sheet;
    }

}
